import { CategoryWithChildren, ExtractCategory, ParagraphType, ReportParagraphBlock, Species, SpeciesDataWithIndexSignatures, SpeciesInfo, TableGenerateResult, Taxon } from "./typings/index";

const getUUID = () => {
    var chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('')
    let uuid = []
    let i = 0;
    let len = 16;
    let radix = chars.length
    if (len) {
        for (i = 0; i < len; i++) uuid[i] = chars[0 | Math.random() * radix]
    } else {
        let r
        uuid[8] = uuid[13] = uuid[18] = uuid[23] = '-'
        uuid[14] = '4'
        for (i = 0; i < 36; i++) {
            if (!uuid[i]) {
                r = 0 | Math.random() * 16
                uuid[i] = chars[(i == 19) ? (r & 0x3) | 0x8 : r]
            }
        }
    }
    return uuid.join('');
}

const adaptNationProtectionLevel = (level: string) => {
    if (
        level == '一级' ||
        level == 'Ⅰ级'
    ) {
        return 'Ⅰ';
    } else if (
        level == '二级' ||
        level == 'Ⅱ级' ||
        level == 'ⅠⅠ级'
    ) {
        return 'Ⅱ'
    }
    return level;
}

function classifyOrganismV2(species: SpeciesInfo): string {
    const classes = species.class;
    if (!classes) {
        return "未知"
    }
    switch (species.kingdom) {
        case 'Plantae': // 植物界
        case 'PLANTAE': // 植物界
            switch (classes) {
                case 'Magnoliopsida': // 被子植物纲
                case 'MAGNOLIOPSIDA':
                case 'Liliopsida':    // 单子叶植物纲
                case 'LILIOPSIDA':
                    return '被子植物';
                case 'Pinopsida':     // 松柏纲
                case 'PINOPSIDA':
                    return '裸子植物';
                default:
                    if (
                        ['Polypodiopsida', 'Marattiopsida', 'Ophioglossopsida'].includes(classes) ||
                        ['Polypodiopsida', 'Marattiopsida', 'Ophioglossopsida'].map(str => str.toUpperCase()).includes(classes) ||
                        ['Pteridophyta', 'Filicinophyta'].includes(species.phylum) ||
                        ['Pteridophyta', 'Filicinophyta'].map(str => str.toUpperCase()).includes(species.phylum) ||
                        ['Equisetopsida', 'Psilotopsida'].includes(classes) ||
                        ['Equisetopsida', 'Psilotopsida'].map(str => str.toUpperCase()).includes(classes)
                    ) {
                        return '蕨类植物';
                    }
                    return '其他植物';
            }
        case 'Animalia': // 动物界
        case 'ANIMALIA':
            switch (species.phylum) {
                case 'Chordata': // 脊索动物门
                case 'CHORDATA':
                    switch (classes) {
                        case 'Mammalia': // 哺乳纲
                        case "MAMMALIA":
                            return '兽类';
                        case 'Aves': // 鸟纲
                        case 'AVES':
                            return '鸟类';
                        case 'Amphibia': // 两栖纲
                        case 'AMPHIBIA':
                        case 'Reptilia': // 爬行纲
                        case 'REPTILIA':
                            return '两爬类';
                        case 'Actinopterygii': //辐鳍鱼纲
                        case 'ACTINOPTERYGII':
                            return '鱼类';
                        default:
                            return '其他脊索动物';
                    }
                case 'Arthropoda': // 节肢动物门
                case 'ARTHROPODA':
                    switch (classes) {
                        case 'Arachnida': // 蛛形纲
                        case 'ARACHNIDA':
                            // 蜘蛛类';
                            return '两爬类';
                        default:
                            //'其他节肢动物'
                            return '两爬类';
                    }
                default:
                    return '其他动物';
            }
        case 'Fungi': // 真菌界
        case 'FUNGI': // 真菌界
            return '大型真菌';
        default:
            return '未知';
    }
}

function generateSpeciesStatistics(data: SpeciesDataWithIndexSignatures[]): Record<string, CategoryWithChildren> {
    let statistics: Record<string, CategoryWithChildren> = {};

    for (const organism of data) {
        let category = classifyOrganismV2(organism);
        if (!statistics[category]) {
            statistics[category] = {};
        }
        let currentTaxonLevel = statistics[category];

        for (const taxonLevel of ["order", "family"]) {
            const taxonKey = taxonLevel + "_c";
            const taxonLatinKey = taxonLevel;
            if (!currentTaxonLevel[organism[taxonKey]]) {
                currentTaxonLevel[organism[taxonKey]] = {
                    name: organism[taxonKey],
                    latin: organism[taxonLatinKey],
                    children: {},
                };
            }
            currentTaxonLevel = (currentTaxonLevel[organism[taxonKey]] as Taxon).children;
        }

        const genusKey = organism.genus_c;
        if (!currentTaxonLevel[genusKey]) {
            currentTaxonLevel[genusKey] = {
                name: organism.genus_c,
                latin: organism.genus,
                children: {},
            };
        }

        currentTaxonLevel = (currentTaxonLevel[genusKey] as Taxon).children;

        if (!currentTaxonLevel[organism.species_c]) {
            const speciesObj: Species = {
                name: organism.species_c,
                latin: organism.canorical_me,
                national: adaptNationProtectionLevel(organism.state_protection_grade),
                provincial: '',
                iucn: organism.endangered,
                cites: organism.CIETS ? `附录${organism.CIETS}` : '',
            };
            currentTaxonLevel[organism.species_c] = speciesObj;
        }
    }

    return statistics;
}



const extractSpeciesStatisticsTotalCategory = (
    speciesCategory: CategoryWithChildren,
    extractCategory: ExtractCategory
): number => {
    let totalCount = 0;
    if (!speciesCategory) {
        return totalCount;
    }
    for (const orderKey in speciesCategory) {
        if (extractCategory == 'order') {
            totalCount++;
        }
        const orderRecord = speciesCategory[orderKey].children;
        for (const familyKey in orderRecord) {
            if (extractCategory == 'family') {
                totalCount++;
            }
            const familyRecord = orderRecord[familyKey].children;
            for (const genusKey in familyRecord) {
                if (extractCategory == 'genus') {
                    totalCount++;
                }
                const genusRecord = familyRecord[genusKey].children;
                for (const speciesKey in genusRecord) {
                    if (extractCategory == 'species') {
                        totalCount++;
                    }
                }
            }
        }
    }
    return totalCount;
}

const checkIsAnyProtectionSpecies = (species: Species): boolean => {
    if (
        species.cites ||
        species.iucn ||
        species.provincial ||
        species.national
    ) {
        return true;
    }
    return false;
}

const checkIsNationalProtectionSpecies = (species: Species): boolean => {
    console.log("checkIsNationalProtectionSpecies--->", species)
    if (
        species.national
    ) {
        return true;
    }
    return false;
}

/**
 * 统计保护一级保护物种的科属种数量
 * 统计保护二级保护物种的科属种数量
 * @param speciesCategory 
 * @returns 
 */
const extractNationalProtectionSpeciesStatisticsTotalCategory = (
    speciesCategory: CategoryWithChildren,
): {
    uniquenOrderNameList1: string[],
    uniquenFamilyNameList1: string[],
    uniquenGenusNameList1: string[],
    uniquenSpeciesNameList1: string[],
    //
    uniquenOrderNameList2: string[],
    uniquenFamilyNameList2: string[],
    uniquenGenusNameList2: string[],
    uniquenSpeciesNameList2: string[],
} => {
    //国家一级保护物种
    let uniquenOrderNameList1: string[] = [];
    let uniquenFamilyNameList1: string[] = [];
    let uniquenGenusNameList1: string[] = [];
    let uniquenSpeciesNameList1: string[] = [];
    //国家二级保护物种
    let uniquenOrderNameList2: string[] = [];
    let uniquenFamilyNameList2: string[] = [];
    let uniquenGenusNameList2: string[] = [];
    let uniquenSpeciesNameList2: string[] = [];
    for (const orderKey in speciesCategory) {
        const orderRecord = speciesCategory[orderKey].children;
        for (const familyKey in orderRecord) {
            const familyRecord = orderRecord[familyKey].children;
            for (const genusKey in familyRecord) {
                const genusRecord = familyRecord[genusKey].children;
                for (const speciesKey in genusRecord) {
                    //@ts-ignore
                    const speciesRecord: Species = genusRecord[speciesKey];
                    if (checkIsNationalProtectionSpecies(speciesRecord)) {
                        // console.log("是国家级保护植物---->", speciesRecord);
                        if (speciesRecord.national == 'Ⅰ') {
                            if (!uniquenOrderNameList1.includes(orderKey)) {
                                uniquenOrderNameList1.push(orderKey)
                            }
                            if (!uniquenFamilyNameList1.includes(familyKey)) {
                                uniquenFamilyNameList1.push(familyKey)
                            }
                            if (!uniquenGenusNameList1.includes(genusKey)) {
                                uniquenGenusNameList1.push(genusKey)
                            }
                            if (!uniquenSpeciesNameList1.includes(speciesRecord.name)) {
                                uniquenSpeciesNameList1.push(speciesRecord.name)
                            }
                        } else {
                            //二级
                            if (!uniquenOrderNameList2.includes(orderKey)) {
                                uniquenOrderNameList2.push(orderKey)
                            }
                            if (!uniquenFamilyNameList2.includes(familyKey)) {
                                uniquenFamilyNameList2.push(familyKey)
                            }
                            if (!uniquenGenusNameList2.includes(genusKey)) {
                                uniquenGenusNameList2.push(genusKey)
                            }
                            if (!uniquenSpeciesNameList2.includes(speciesRecord.name)) {
                                uniquenSpeciesNameList2.push(speciesRecord.name)
                            }
                        }
                    }
                }
            }
        }
    }
    return {
        uniquenOrderNameList1,
        uniquenFamilyNameList1,
        uniquenGenusNameList1,
        uniquenSpeciesNameList1,
        //
        uniquenOrderNameList2,
        uniquenFamilyNameList2,
        uniquenGenusNameList2,
        uniquenSpeciesNameList2,
    };
}

const getProportionValue = (value: number, totalValue: number): string => {
    if(totalValue == 0){
        return "0"
    }
    return (Number(value) / Number(totalValue) * 100).toFixed(2);
}

const generateWordParagraBlockByPlainText = (
    plainText: string,
    paragraphType: ParagraphType,
    pageBreakBefore?: boolean,
    pageBreakAfter?: boolean
): ReportParagraphBlock => {
    let paragrahBlock: ReportParagraphBlock = {
        blockId: getUUID(),
        type: 'Text',
        paragraphInlineStyleRanges: [{
            offset: 0,
            length: plainText.length,
            name: 'inlineStyle_normal',
        }],
        paragraphType: paragraphType,
        text: plainText,
        pageBreakBefore: pageBreakBefore ? pageBreakBefore : false,
        pageBreakAfter: pageBreakAfter ? pageBreakAfter : false,
    }
    return paragrahBlock;
}

const isEmpty = (value: any) => {
    try {
        if (value === null || value === undefined || value === "" || JSON.stringify(value) === '{}') {
            return true
        };
        return false;
    } catch (e) {
        return true;
    }
};

const generateWordParagraBlockByTableGenerateResult = (
    tableGenerateResult: TableGenerateResult
): ReportParagraphBlock => {
    const {
        tableData = [[]],
        tableHeaderRowNumber = 1,
        tableStyle = 'normalTable',
        tableRowSpanInfoList = [],
        tableColSpanInfoList = []
    } = tableGenerateResult;
    let paragrahBlock: ReportParagraphBlock = {
        blockId: getUUID(),
        type: 'Table',
        paragraphType: 'normalParagraph',
        tableData: tableData,
        tableHeaderRowNumber: tableHeaderRowNumber,
        tableStyle: tableStyle,
        rowSpanInfoList: tableRowSpanInfoList,
        colSpanInfoList: tableColSpanInfoList
    }
    return paragrahBlock;
}

const getChineseNumber = (num: number): string => {
    if (num < 0 || num > 1000 || !Number.isInteger(num)) {
        return num.toString();
    }
    const chineseNumbers = ['', '一', '二', '三', '四', '五', '六', '七', '八', '九'];
    const chineseTens = ['', '十', '百', '千'];
    const numStr = num.toString();
    const len = numStr.length;
    let chineseNum = '';
    for (let i = 0; i < len; i++) {
        const digit = Number(numStr[i]);
        if (digit !== 0) {
            chineseNum += chineseNumbers[digit] + chineseTens[len - i - 1];
        } else if (len > 1 && Number(numStr.slice(i + 1)) > 0) { // For numbers like 100, 200, etc.
            chineseNum += '零';
        }
    }
    // Special case for numbers between 11 and 19
    if (num > 10 && num < 20) {
        return '十' + chineseNumbers[num % 10];
    }
    // Special case for numbers like 20, 30, 40, etc.
    if (num % 10 === 0) {
        return chineseNum.slice(0, -1); // Remove the trailing zero
    }
    return chineseNum;
}

const isNumber = (value: any): boolean => {
    try {
        if (!isEmpty(value) && !Number.isNaN(Number(value))) {
            return true;
        }
        return false;
    } catch (e) {
        console.log("isNumber---err", e)
        return false;
    }
}

//计算某一列的平均值
const caculateSheetTargtColumnValue = (sheetData: any[], tagetKey: string, type: 'total' | 'avg' | 'max' | 'min') => {

    let totalValue = 0;
    let avgValue = 0;
    let maxValue = -999999;
    let minValue = 999999;
    let totalCount = 0;
    sheetData.forEach((row, rowIndex) => {
        if (row[tagetKey] && isNumber(row[tagetKey])) {
            const tempValue: number = Number(row[tagetKey]);
            if (tagetKey == "PH") {
                console.log("tempValue--->", row, tempValue)
            }
            totalCount++;
            totalValue += tempValue;
            if (maxValue < tempValue) {
                maxValue = tempValue;
            }
            if (minValue > tempValue) {
                minValue = tempValue;
            }
        }
    })
    if (maxValue == -999999) {
        //@ts-ignore
        maxValue = ''
    }
    if (minValue == 999999) {
        //@ts-ignore
        minValue = ''
    }
    avgValue = totalCount !== 0 ? Number((totalValue / totalCount).toFixed(2)) : 0;
    switch (type) {
        case 'avg':
            if (tagetKey == "PH") {
                console.log("avg--->", totalValue, totalCount, avgValue)
            }
            return avgValue;
            break;
        case 'max':
            return maxValue;
            break;
        case 'min':
            return minValue;
            break;
        case 'total':
            return totalValue;
            break;
        default:
            break;
    }
}

//获取表格中某一列的去重值
const getSheetUniquenTargetValueList = (sheetData: any[], tagetKey: string) => {
    let uniquenTargetValueList: string[] = [];
    sheetData.forEach((row, rowIndex) => {
        if (!isEmpty(row[tagetKey])) {
            if (!uniquenTargetValueList.includes(row[tagetKey])) {
                uniquenTargetValueList.push(row[tagetKey]);
            }
        }
    })
    return uniquenTargetValueList;
}

const excelDateToJSDate = (excelDate: number) => {
    // Excel的起始日期（1900年1月0日），JavaScript中的月份是从0开始计数的
    const startDate = new Date(1900, 0, 1);

    // 由于Excel错误地将1900年视为闰年，当日期大于等于60时，需要减去1天
    const daysToAdjust = excelDate > 60 ? excelDate - 1 : excelDate;

    // 将天数转换为毫秒并添加到起始日期
    const jsDate = new Date(startDate.getTime() + (daysToAdjust - 1) * 86400000);

    // 格式化日期为 "年-月-日"，确保月份和日期为两位数
    const year = jsDate.getFullYear();
    const month = (jsDate.getMonth() + 1).toString().padStart(2, '0'); // 月份+1，因为getMonth()返回0-11
    const day = jsDate.getDate().toString().padStart(2, '0');

    return `${year}-${month}-${day}`;
}

const excelDateToJSDateWithMonthText = (excelDate: number) => {
    // Excel的起始日期（1900年1月0日），JavaScript中的月份是从0开始计数的
    const startDate = new Date(1900, 0, 1);

    // 由于Excel错误地将1900年视为闰年，当日期大于等于60时，需要减去1天
    const daysToAdjust = excelDate > 60 ? excelDate - 1 : excelDate;

    // 将天数转换为毫秒并添加到起始日期
    const jsDate = new Date(startDate.getTime() + (daysToAdjust - 1) * 86400000);

    // 格式化日期，月份+1，因为getMonth()返回0-11，并添加“月”字样
    const year = jsDate.getFullYear();
    const month = jsDate.getMonth() + 1; // 月份+1
    const day = jsDate.getDate().toString().padStart(2, '0');
    return `${month}月`;
}

const getStrMonth = (str: string) => {
    try {
        if (isNumber(str)) {
            return excelDateToJSDateWithMonthText(Number(str));
        } else {
            let strList = str.split('月');
            return strList[0] + '月';
        }
    } catch (e) {
        console.log("getStrMonth--->err", e, str)
        return '未知月份'
    }
}


const getAvgSheetDataByMonth = (sheetData: any[], tagetKey: string) => {
    const stationList = getSheetUniquenTargetValueList(sheetData, '站点名称');
    let stationDataList: { stationName: string, dataList: { month: string, value: number }[] }[] = [];
    stationList.forEach(stationName => {
        stationDataList.push({
            stationName,
            dataList: []
        })
    })
    sheetData.forEach((row, rowIndex) => {
        let findStationIndex = -1;
        stationDataList.forEach((stationData, stationDataIndex) => {
            if (stationData.stationName == row['站点名称']) {
                findStationIndex = stationDataIndex;
            }
        })
        if (findStationIndex !== -1) {
            const tempMonth = getStrMonth(row['监测时间']);
            const tempValue = row[tagetKey];
            stationDataList[findStationIndex].dataList.push({
                month: tempMonth,
                value: tempValue
            })
        }
    })
    let sheetAvgMonthData: { "站点名称": string, "监测时间": string, "平均值": string }[] = [];
    stationDataList.forEach(stationData => {
        let mounthDataList: { stationName: string, month: string, valueList: number[] }[] = [];
        stationData.dataList.forEach(dataObj => {
            let findStationMonthDataListIndex = -1;
            mounthDataList.forEach((ele, index) => {
                if (ele.stationName == stationData.stationName && ele.month == dataObj.month) {
                    findStationMonthDataListIndex = index;
                }
            })
            if (findStationMonthDataListIndex !== -1) {
                mounthDataList[findStationMonthDataListIndex].valueList.push(dataObj.value)
            } else {
                mounthDataList.push({
                    month: dataObj.month,
                    stationName: stationData.stationName,
                    valueList: [dataObj.value]
                })
            }
        })
        // console.log("mounthDataList--->", mounthDataList)
        mounthDataList.forEach(monthData => {
            let avgValue = 0;
            let totalCount = 0;
            let totalValue = 0;
            monthData.valueList.forEach(ele => {
                if (!isEmpty(ele) && isNumber(ele)) {
                    totalCount++;
                    totalValue += Number(ele)
                }
            })
            avgValue = Number((totalValue / totalCount).toFixed(2))
            sheetAvgMonthData.push({
                "站点名称": monthData.stationName,
                "平均值": avgValue + '',
                "监测时间": monthData.month
            })
        })
    })
    // console.log("stationDataList--->", stationDataList);
    // console.log("sheetAvgMonthData--->", sheetAvgMonthData)
    return sheetAvgMonthData;
}

const caculateSheetTargtColumnValueMonth = (sheetData: any[], tagetKey: string, type: 'max' | 'min'): { month: string, name: string, value: string } => {
    let maxValue = -999999;
    let maxValueMonth = '';
    let maxValueStationName = '';
    let minValue = 999999;
    let minValueMonth = '';
    let minValueStationName = '';
    let result = {
        name: '',
        month: '',
        value: '',
    }
    // console.log("sheetData---->", sheetData)
    sheetData.forEach((row, rowIndex) => {
        if (row[tagetKey] && isNumber(row[tagetKey])) {
            const tempValue: number = Number(row[tagetKey]);
            console.log("tempValue--->", tempValue)
            if (maxValue < tempValue) {
                maxValue = tempValue;
                maxValueMonth = getStrMonth(row['监测时间']);
                maxValueStationName = row['站点名称'];
            }
            if (minValue > tempValue) {
                console.log("minValue---->", minValue + '----->' + tempValue)
                minValue = tempValue;
                minValueMonth = getStrMonth(row['监测时间']);
                minValueStationName = row['站点名称'];
            }
        }
    })
    if (maxValue == -999999) {
        //@ts-ignore
        maxValue = ''
    }
    if (minValue == 999999) {
        //@ts-ignore
        minValue = ''
    }
    switch (type) {
        case 'max':
            result.month = maxValueMonth;
            result.value = maxValue + '';
            result.name = maxValueStationName;
            // console.log("result-max--->", result);
            return result;
            break;
        case 'min':
            result.month = minValueMonth;
            result.value = minValue + '';
            result.name = minValueStationName;
            // console.log("result-min--->", result);
            return result;
            break;
        default:
            break;
    }
    return result
}

const caculateValueList = (valueList: string[], type: 'avg' | 'max' | 'min' | 'total') => {
    let totalValue = 0;
    let avgValue = 0;
    let maxValue = -999999;
    let minValue = 999999;
    let totalCount = 0;
    valueList.forEach((value, index) => {
        if (!isEmpty(value) && isNumber(value)) {
            const tempValue: number = Number(value);
            totalCount++;
            totalValue += tempValue;
            if (maxValue < tempValue) {
                maxValue = tempValue;
            }
            if (minValue > tempValue) {
                minValue = tempValue;
            }
        }
    })
    avgValue = Number((totalValue / totalCount).toFixed(2));
    //@ts-ignore
    if (avgValue == 'NaN' || Number.isNaN(avgValue) || avgValue == NaN) {
        //@ts-ignore
        avgValue = '';
    }
    //@ts-ignore
    if (maxValue == 'NaN' || Number.isNaN(maxValue) || maxValue == NaN || maxValue == -999999) {
        //@ts-ignore
        maxValue = '';
    }
    //@ts-ignore
    if (minValue == 'NaN' || Number.isNaN(minValue) || minValue == NaN || minValue == 999999) {
        //@ts-ignore
        minValue = '';
    }
    switch (type) {
        case 'avg':
            return avgValue;
            break;
        case 'max':
            return maxValue;
            break;
        case 'min':
            return minValue;
            break;
        case 'total':
            return totalValue;
            break;
        default:
            break;
    }
}

const caculateSheetTargtColumnValueMergedByMonth = (sheetData: any[], tagetKey: string, type: 'avg' | 'max' | 'min'): string[][] => {
    const monthNameList: string[] = [];
    const stationNameList: string[] = [];
    sheetData.forEach(row => {
        const monthName = getStrMonth(row['监测时间']);
        if (!monthNameList.includes(monthName)) {
            monthNameList.push(monthName)
        }
        const stationName = row['站点名称'];
        if (!stationNameList.includes(stationName)) {
            stationNameList.push(stationName)
        }
    })
    let dataRecordList: { month: string, stationDataList: { stationName: string, valueList: any[] }[] }[] = [];
    monthNameList.forEach(month => {
        let stationDataList: { stationName: string, valueList: any[] }[] = [];
        stationNameList.forEach(stationName => {
            stationDataList.push({
                stationName: stationName,
                valueList: []
            })
        })
        dataRecordList.push({
            month: month,
            stationDataList: stationDataList
        })
    })
    sheetData.forEach(row => {
        const monthName = getStrMonth(row['监测时间']);
        const stationName = row['站点名称'];
        // console.log("stationName--->", stationName)
        let findMonthIndex = -1;
        dataRecordList.forEach((ele, index) => {
            if (ele.month == monthName) {
                findMonthIndex = index;
            }
        })
        if (findMonthIndex !== -1) {
            let findStationIndex = -1;
            dataRecordList[findMonthIndex].stationDataList.forEach((ele, index) => {
                if (ele.stationName == stationName) {
                    findStationIndex = index;
                }
            })
            if (findStationIndex !== -1) {
                // console.log("findStationIndex --->", findStationIndex)
                dataRecordList[findMonthIndex].stationDataList[findStationIndex].valueList.push(row[tagetKey])
            }
        }
    })
    // console.log("dataRecordList--->", dataRecordList);
    let resultSheetData: string[][] = [['月 份']];
    stationNameList.forEach(stationName => {
        resultSheetData[0].push(stationName);
    })
    resultSheetData[0].push("全区")
    dataRecordList.forEach(monthRecord => {
        let rowTemplate: string[] = [];
        monthRecord.stationDataList.forEach(stationRecord => {
            let currentStationValue = caculateValueList(stationRecord.valueList, type);
            rowTemplate.push(currentStationValue + '');
        })
        rowTemplate.push(caculateValueList(rowTemplate, type) + '');
        rowTemplate.unshift(monthRecord.month);
        resultSheetData.push(rowTemplate);
    })
    // console.log("resultSheetData--->", resultSheetData)
    return resultSheetData;
}

/**
 * 获取指定列的固定行区间的总值
 * @param tableData 
 * @param colIndex 
 * @param startRowIndex 
 * @param endRowIndex 
 * @returns 
 */
const getCellValueTotalByColGroup = (
    tableData: string[][],
    colIndex: number,
    startRowIndex: number,
    endRowIndex: number,
) => {
    let columnList: any[] = [];
    tableData.forEach((rowList, rowIndex) => {
        if (rowIndex >= startRowIndex && rowIndex <= endRowIndex) {
            columnList.push(rowList[colIndex])
        }
    })
    let totalValue = 0;
    columnList.forEach((value, colIndex) => {
        if (!Number.isNaN(value)) {
            totalValue += Number(value);
        }
    })
    return totalValue;
}

/**
 * 获取指定列的固定行区间的去重值
 * @param tableData 
 * @param colIndex 
 * @param startRowIndex 
 * @param endRowIndex 
 * @returns 
 */
const getCellValueTotalUniqueCountByColGroup = (
    tableData: string[][],
    colIndex: number,
    startRowIndex: number,
    endRowIndex: number,
) => {
    let columnList: any = [];
    tableData.forEach((rowList, rowIndex) => {
        if (rowIndex >= startRowIndex && rowIndex <= endRowIndex) {
            columnList.push(rowList[colIndex])
        }
    })
    let uniqueValueList: any[] = [];
    //@ts-ignore
    columnList.forEach((value, colIndex) => {
        if (!uniqueValueList.includes(value)) {
            uniqueValueList.push(value)
        }
    })
    uniqueValueList = uniqueValueList.filter(item => {
        return !isEmpty(item)
    })
    return uniqueValueList.length;
}

/**
 * 获取指定行的固定列区间的总值
 * @param tableData 
 * @param rowIndex 
 * @param startColIndex 
 * @param endColIndex 
 */
const getCellValueTotalByRowGroup = (
    tableData: string[][],
    rowIndex: number,
    startColIndex: number,
    endColIndex: number,
) => {
    let rowList = tableData[rowIndex];
    let totalValue = 0;
    rowList.forEach((value, colIndex) => {
        if (
            !Number.isNaN(value) &&
            colIndex >= startColIndex &&
            colIndex <= endColIndex
        ) {
            totalValue += Number(value);
        }
    })
    return totalValue;
}

/**
 * 获取表格区域的总值
 * @param tableData 
 * @param startRowIndex 
 * @param endRowIndex 
 * @param startColIndex 
 * @param endColIndex 
 */
const getTableMatrixTotalValue = (
    tableData: string[][],
    startRowIndex: number,
    endRowIndex: number,
    startColIndex: number,
    endColIndex: number
) => {
    let totalValue = 0;
    tableData.forEach((rowList, rowIndex) => {
        rowList.forEach((value, colIndex) => {
            if (!Number.isNaN(Number(value))) {
                totalValue += Number(value)
            }
        })
    })
    return totalValue;
}


/**
 * 获取表格区域最大的行总值
 * @param tableData 
 * @param startRowIndex 
 * @param endRowIndex 
 * @param startColIndex 
 * @param endColIndex 
 */
const getTableMatrixRowTotalValueMax = (
    tableData: string[][],
    startRowIndex: number,
    endRowIndex: number,
    startColIndex: number,
    endColIndex: number
) => {
    let rowTotalValueMax = 0;
    tableData.forEach((rowList, rowIndex) => {
        if (
            rowIndex >= startRowIndex &&
            rowIndex <= endRowIndex
        ) {
            let tempRowTotalValue = 0;
            rowList.forEach((value, colIndex) => {
                if (
                    colIndex >= startColIndex &&
                    colIndex <= endColIndex
                ) {
                    tempRowTotalValue += Number(value)
                }
            })
            if (tempRowTotalValue > rowTotalValueMax) {
                rowTotalValueMax = tempRowTotalValue;
            }
        }
    })
    return rowTotalValueMax;
}


const getPercent = (value1: number, value2: number) => {
    return ((Number(value1) / Number(value2)) * 100).toFixed(2)
}

const replaceUnitUpperCase = (str: string) => {
    try {
        const regex = /(m2|m3)/g;
        let resultStr = str.replace(regex, (matched) => {
            return matched === "m2" ? "㎡" : "m³";
        })
        return resultStr;
    } catch (e) {
        return str;
    }
}

const presetWorkSheet = (originWorkSheet: string[][]) => {
    let newWorkSheet: string[][] = [];
    originWorkSheet.forEach(rowList => {
        let newRowList: any = [];
        rowList.forEach(cellValue => {
            const replacedCellValue = replaceUnitUpperCase(cellValue);
            newRowList.push(replacedCellValue)
        })
        newWorkSheet.push(newRowList)
    })
    return newWorkSheet;
}

function removeAndAdjustColumn(
    tabledata: string[][],
    colIndex: number,
    rowSpanList: any[],
    colSpanList: any[]
) {
    // 1. 删除指定列
    const adjustedData = tabledata.map(row => {
        const newRow = [...row];
        newRow.splice(colIndex, 1);
        return newRow;
    });

    // 2. 调整行和列合并
    rowSpanList.forEach(item => {
        if (item.colIndex === colIndex) {
            const index = rowSpanList.indexOf(item);
            rowSpanList.splice(index, 1);
        } else if (item.colIndex > colIndex) {
            item.colIndex--;
        }
    });

    colSpanList.forEach(item => {
        if (item.colIndex <= colIndex && colIndex < item.colIndex + item.colSpan) {
            item.deleteCount++;
            if (item.deleteCount === item.originColSpan) {
                const index = colSpanList.indexOf(item);
                colSpanList.splice(index, 1);
            } else {
                item.colSpan--;
            }
        } else if (item.colIndex > colIndex) {
            item.colIndex--;
        }
    });

    return adjustedData;
}

const processWordParagraphBlockList = (wordParagraphBlockList: ReportParagraphBlock[]): ReportParagraphBlock[] => {
    wordParagraphBlockList.forEach(wordParagraphBlock => {
        if (isEmpty(wordParagraphBlock.inlineStyleRanges)) {
            if (isEmpty(wordParagraphBlock.text)) {
                wordParagraphBlock.text = '表格';
            }
            //@ts-ignore
            wordParagraphBlock.inlineStyleRanges = [{ offset: 0, length: wordParagraphBlock.text.length, style: 'normal_inline_style' }]
        }
        if (wordParagraphBlock.type == 'Table') {
            let newTableData: string[][] = [];
            wordParagraphBlock.tableData?.forEach(rowList => {
                let newRowList: string[] = [];
                rowList.forEach(cellText => {
                    newRowList.push(cellText + "")
                })
                newTableData.push(newRowList)
            })
            wordParagraphBlock.tableData = [...newTableData];
        }
    })
    return wordParagraphBlockList;
}

export {
    isEmpty,
    getUUID,
    isNumber,
    getChineseNumber,
    getProportionValue,
    classifyOrganismV2,
    adaptNationProtectionLevel,
    generateSpeciesStatistics,
    extractSpeciesStatisticsTotalCategory,
    extractNationalProtectionSpeciesStatisticsTotalCategory,
    generateWordParagraBlockByPlainText,
    generateWordParagraBlockByTableGenerateResult,
    caculateSheetTargtColumnValue,
    getSheetUniquenTargetValueList,
    caculateSheetTargtColumnValueMonth,
    getAvgSheetDataByMonth,
    caculateSheetTargtColumnValueMergedByMonth,
    getCellValueTotalByColGroup,
    getCellValueTotalUniqueCountByColGroup,
    getCellValueTotalByRowGroup,
    getTableMatrixTotalValue,
    getTableMatrixRowTotalValueMax,
    getPercent,
    presetWorkSheet,
    removeAndAdjustColumn,
    processWordParagraphBlockList
}